﻿using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace MakeItWeb.Framework
{
    public abstract class DataAccess
    {
        /// <summary>
        /// Get DataSet from the SQL query string
        /// </summary>
        /// <param name="sSql">SQL query string</param>
        /// <returns>DataSet of the recordset</returns>
        public static DataSet GetDataSet(string sSql)
        {
            using (SqlConnection cn = OpenConnection())
            {
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = new SqlCommand(sSql, cn);
                DataSet dsSql = new DataSet();
                da.Fill(dsSql);
                return dsSql;
            }
        }

        private static SqlConnection OpenConnection()
        {
            return new SqlConnection(ConfigurationManager.ConnectionStrings["DbConn"].ConnectionString);
        }

        public static DataSet GetDataSet(string sStoredProc, Dictionary<string, object> paramList)
        {
            return GetDataSet(sStoredProc, paramList, true);
        }

        public static DataSet GetDataSet(string sStoredProc, Dictionary<string, object> paramList, bool bUseCache)
        {
            using (SqlConnection cn = OpenConnection())
            {
                SqlDataAdapter da = new SqlDataAdapter();

                string sCacheKey = sStoredProc;
                SqlCommand oCommand = new SqlCommand(sStoredProc, cn);
                oCommand.CommandType = CommandType.StoredProcedure;
                // add parameters
                foreach (string sKey in paramList.Keys)
                {
                    oCommand.Parameters.AddWithValue(string.Format("@{0}", sKey), paramList[sKey]);
                    sCacheKey = string.Format("{0} {1}={2}", sCacheKey, sKey, paramList[sKey]);
                }

                // if the oCommand is already stored in the current request, return it instead.
                if (bUseCache && System.Web.HttpContext.Current.Items.Contains(sCacheKey))
                    return System.Web.HttpContext.Current.Items[sCacheKey] as DataSet;

                da.SelectCommand = oCommand;
                DataSet dsSql = new DataSet();
                da.Fill(dsSql);

                // store it in case same stored proc is called again in the same request.
                System.Web.HttpContext.Current.Items[sCacheKey] = dsSql;

                return dsSql;
            }
        }

        public static void ExecuteCud(string sStoredProc, Dictionary<string, object> paramList)
        {
            using (SqlConnection cn = OpenConnection())
            {
                SqlCommand oCommand = new SqlCommand(sStoredProc, cn);
                oCommand.CommandType = CommandType.StoredProcedure;
                // add parameters
                foreach (string sKey in paramList.Keys)
                    oCommand.Parameters.AddWithValue(string.Format("@{0}", sKey), paramList[sKey]);
                cn.Open();
                oCommand.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Currently output parameter is only supported for int datatype.
        /// </summary>
        /// <param name="sStoredProc">Stored procedure name.</param>
        /// <param name="paramList">input parameters.</param>
        /// <param name="outParamList">output parameters.</param>
        public static int ExecuteCud(string sStoredProc, Dictionary<string, object> paramList, string sOutParamName)
        {
            using (SqlConnection cn = OpenConnection())
            {
                SqlCommand oCommand = new SqlCommand(sStoredProc, cn);
                oCommand.CommandType = CommandType.StoredProcedure;
                // add parameters
                foreach (string sKey in paramList.Keys)
                    oCommand.Parameters.AddWithValue(string.Format("@{0}", sKey), paramList[sKey]);


                SqlParameter param = oCommand.Parameters.Add(string.Format("@{0}", sOutParamName), SqlDbType.Int);
                param.Direction = ParameterDirection.Output;

                cn.Open();

                oCommand.ExecuteNonQuery();

                return (int)param.Value;
            }
        }
    }
}